from datetime import datetime

from impala.dbapi import connect
# 导入as_pandas工具，可以将获取到的数据转化为 dataframe或者 Series
from impala.util import as_pandas
import pandas as pd
# 得到连接，
class SQL:

    def __init__(self, url, db_name, user, password):
        self.url = url
        self.db_name = db_name
        self.user = user
        self.password = password

    def get_conn(self):
        conn = conn = connect(host='remote', port=10000, auth_mechanism='PLAIN', user='root', password='CODEcode-123',  database='weather_base')
        print('connecting successfully.')
        return conn

    def get_weather_by_city(self, city):
        # 得到句柄
        conn = self.get_conn()
        cursor = conn.cursor()
        # 执行查询
        # cursor.execute('show tables')
        sql = '''
            select * from t_weather
            where city = '{}'
        '''.format(city)

        cursor.execute(sql)
        # 将结果放入dataframe中显示
        df = as_pandas(cursor)
        # 关闭连接
        cursor.close()
        conn.close()

        return pd.DataFrame.to_json(df, orient="index", force_ascii=False)

    def get_weather_by_area(self, area):
        # 得到句柄
        conn = self.get_conn()
        cursor = conn.cursor()
        # 执行查询
        # cursor.execute('show tables')
        sql = '''
            select * from t_weather
            where area = '{}'
        '''.format(area)

        cursor.execute(sql)
        # 将结果放入dataframe中显示
        df = as_pandas(cursor)
        # 关闭连接
        cursor.close()
        conn.close()

        df.columns = ['id', 'date', 'max_temp', 'min_temp', 'rain_poss', 'area', 'city', 'province_name']
        return pd.DataFrame.to_json(df, orient="index", force_ascii=False)


    def get_weather_by_area_and_date(self, area, date):
        conn = self.get_conn()
        cursor = conn.cursor()
        sql = '''
            select * from t_weather
            where area = '{}' AND `date` = {}
        '''.format(area, date)

        cursor.execute(sql)
        df = as_pandas(cursor)

        df.columns = ['id', 'date', 'max_temp', 'min_temp', 'rain_poss', 'area', 'city', 'province_name']
        return pd.DataFrame.to_json(df, orient="index", force_ascii=False)

    def get_map_weather_max_temp(self):
        '''
        :return: 返回用于echarts的json格式
        name: 城市名
        value: 最高气温
        '''
        today = str(datetime.now())
        today = today[0:10].replace("-", "")

        conn = self.get_conn()
        cursor = conn.cursor()
        sql = '''
            select area, max_temp from t_weather
            where `date` = {}
        '''.format(today)

        cursor.execute(sql)
        df = as_pandas(cursor)

        df.columns = ['name', 'value']
        return pd.DataFrame.to_json(df, orient="index", force_ascii=False)

    def get_city_with_biggest_temp_diff(self):
        today = str(datetime.now())
        today = today[0:10].replace("-", "")

        conn = self.get_conn()
        cursor = conn.cursor()
        sql = '''
            
                select area, max_temp, min_temp from t_weather
                where `date` = {}
        '''.format(today)
        cursor.execute(sql)
        df = as_pandas(cursor)
        df['temp_diff'] = df['max_temp'] - df['min_temp']
        df = df.sort_values('temp_diff', ascending=False).head(10)
        return pd.DataFrame.to_json(df, orient="index", force_ascii=False)
